<?php
/**
 * 修复用户表缺少username字段的问题
 * 解决云端部署时出现的 "Unknown column 'username' in 'where clause'" 错误
 */

// 显示所有错误
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// 引入配置文件
require_once 'config.php';

echo "<h1>修复用户表username字段缺失问题</h1>";
echo "<div style='font-family: Arial, sans-serif; max-width: 800px; margin: 20px;'>";

// 连接数据库
$conn = getDBConnection();
if (!$conn) {
    die("<p style='color:red'>❌ 数据库连接失败</p>");
}

echo "<p style='color:green'>✅ 数据库连接成功</p>";

// 检查当前表结构
echo "<h2>1. 检查当前users表结构</h2>";
$result = $conn->query("DESCRIBE users");
if ($result) {
    echo "<table border='1' cellpadding='5' style='border-collapse: collapse;'>";
    echo "<tr style='background-color: #f0f0f0;'><th>字段</th><th>类型</th><th>允许NULL</th><th>键</th><th>默认值</th></tr>";
    
    $hasUsername = false;
    while ($row = $result->fetch_assoc()) {
        if ($row['Field'] === 'username') {
            $hasUsername = true;
        }
        echo "<tr>";
        echo "<td>".htmlspecialchars($row['Field'])."</td>";
        echo "<td>".htmlspecialchars($row['Type'])."</td>";
        echo "<td>".htmlspecialchars($row['Null'])."</td>";
        echo "<td>".htmlspecialchars($row['Key'])."</td>";
        echo "<td>".htmlspecialchars($row['Default'])."</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    if ($hasUsername) {
        echo "<p style='color:orange'>⚠️ username字段已存在，无需修复</p>";
    } else {
        echo "<p style='color:red'>❌ 缺少username字段，需要修复</p>";
        
        // 添加username字段
        echo "<h2>2. 添加username字段</h2>";
        $alterSql = "ALTER TABLE users ADD COLUMN username VARCHAR(50) UNIQUE AFTER email";
        
        if ($conn->query($alterSql)) {
            echo "<p style='color:green'>✅ username字段添加成功</p>";
            
            // 为现有用户生成username
            echo "<h2>3. 为现有用户生成username</h2>";
            $users = $conn->query("SELECT id, email FROM users WHERE username IS NULL");
            
            if ($users && $users->num_rows > 0) {
                $updateCount = 0;
                while ($user = $users->fetch_assoc()) {
                    // 从email中提取用户名部分作为username
                    $emailParts = explode('@', $user['email']);
                    $baseUsername = $emailParts[0];
                    
                    // 确保username唯一
                    $username = $baseUsername;
                    $counter = 1;
                    while (true) {
                        $checkStmt = $conn->prepare("SELECT id FROM users WHERE username = ?");
                        $checkStmt->bind_param("s", $username);
                        $checkStmt->execute();
                        $checkResult = $checkStmt->get_result();
                        
                        if ($checkResult->num_rows === 0) {
                            break; // username可用
                        }
                        
                        $username = $baseUsername . $counter;
                        $counter++;
                        $checkStmt->close();
                    }
                    
                    // 更新用户的username
                    $updateStmt = $conn->prepare("UPDATE users SET username = ? WHERE id = ?");
                    $updateStmt->bind_param("si", $username, $user['id']);
                    
                    if ($updateStmt->execute()) {
                        echo "<p>✅ 用户 {$user['email']} 的username设置为: {$username}</p>";
                        $updateCount++;
                    } else {
                        echo "<p style='color:red'>❌ 更新用户 {$user['email']} 失败: {$conn->error}</p>";
                    }
                    
                    $updateStmt->close();
                }
                
                echo "<p style='color:green'>✅ 共更新了 {$updateCount} 个用户的username</p>";
            } else {
                echo "<p>ℹ️ 没有需要更新username的用户</p>";
            }
            
        } else {
            echo "<p style='color:red'>❌ 添加username字段失败: {$conn->error}</p>";
        }
    }
} else {
    echo "<p style='color:red'>❌ 无法获取表结构: {$conn->error}</p>";
}

// 验证修复结果
echo "<h2>4. 验证修复结果</h2>";
$result = $conn->query("DESCRIBE users");
if ($result) {
    $hasUsername = false;
    while ($row = $result->fetch_assoc()) {
        if ($row['Field'] === 'username') {
            $hasUsername = true;
            break;
        }
    }
    
    if ($hasUsername) {
        echo "<p style='color:green'>✅ username字段存在</p>";
        
        // 检查是否有用户数据
        $userCount = $conn->query("SELECT COUNT(*) as count FROM users WHERE username IS NOT NULL");
        if ($userCount) {
            $count = $userCount->fetch_assoc()['count'];
            echo "<p style='color:green'>✅ 有 {$count} 个用户拥有username</p>";
        }
        
        echo "<div style='background-color: #d4edda; border: 1px solid #c3e6cb; padding: 15px; border-radius: 5px; margin: 20px 0;'>";
        echo "<h3 style='color: #155724; margin-top: 0;'>🎉 修复完成！</h3>";
        echo "<p style='color: #155724; margin-bottom: 0;'>现在可以正常使用基于username的登录功能了。</p>";
        echo "</div>";
        
    } else {
        echo "<p style='color:red'>❌ username字段仍然缺失</p>";
    }
}

$conn->close();
echo "</div>";
?>

<style>
body {
    font-family: Arial, sans-serif;
    line-height: 1.6;
    margin: 20px;
    background-color: #f8f9fa;
}

h1, h2 {
    color: #333;
}

table {
    background-color: white;
    margin: 10px 0;
}

th {
    background-color: #007bff;
    color: white;
    padding: 8px;
}

td {
    padding: 8px;
    border: 1px solid #ddd;
}

p {
    margin: 8px 0;
}
</style>